﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using CommunitySystem.BO;
using CommunitySystemModel;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;
using NPOI.HSSF.Util;

namespace CommunitySystem.Web.Service
{
    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码、svc 和配置文件中的类名“JinShengService”。
    public class JinShengService : IJinShengService
    {
        private readonly JinShengBO bo = new JinShengBO();

        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfoAccordingToMianShi(int year, int id, string name)
        {
            return bo.GetEmpBasicInfoAccordingToMianShi(year, id, name);
        }

        public ObservableCollection<JinShen_MainInfo> GetJinShen_MainInfo(string condition, string LoginUser)
        {
            return bo.GetJinshenMainInfo(condition, LoginUser);
        }

        public void SaveJinShenMainInfo(JinShen_MainInfo entity)
        {   
            //if (entity.Status == "晋升确认" && entity.ID > 0)//确认晋升  完成一系列动作
            //{
            //    // 简历信息加一条
            //    // 职务加一条  在任  前面的改为不在任
            //    // Chegai 信息增加一条  
            //    try
            //    {

            //    }

            //}
            //else
            //{
                bo.SaveJinShen_MainInfor(entity);
           // }
        }

        public void DeleteJinShenMainInfo(JinShen_MainInfo entity)
        {
            AccessHelper.GetInstance().DeleteEntity("JinShen_MainInfo", entity.ID);
            foreach (var d in entity.Details)
            {
                AccessHelper.GetInstance().DeleteEntity("JinShen_DetailInfo", entity.ID);
            }
        }

        public void SaveJinShenDetail(JinShen_DetailInfo entity)
        {
            AccessHelper.GetInstance().SaveEntity(entity, "", null);
        }


        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfo(string condition)
        {
            return bo.GetEmpBasicInfo(condition);
        }
        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfo_zj(string condition,int orgid)
        {
            return bo.GetEmpBasicInfo_zj(condition,orgid);
        }

        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfoAccordingToDuty(string condition)
        {
            return bo.GetEmpBasicInfoAccordingToDuty(condition);
        }

        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfoAccordingToDuty_ZJ(string condition, int orgid)
        {
            return bo.GetEmpBasicInfoAccordingToDuty_ZJ(condition, orgid);
        }
        public ObservableCollection<JinShen_MainInfo_Check> GetMainInfo_Check(string condition)
        {
            return AccessHelper.GetInstance().GetEnity<JinShen_MainInfo_Check>(condition, "");
        }

        public bool SaveMainInfo_Check(JinShen_MainInfo_Check entity)
        {
            try
            {

                AccessHelper.GetInstance().SaveEntity(entity, "", null);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public ObservableCollection<JinShen_CompetSpeech_Main> GetCompetSpeechMain(string condition)
        {
            var mains = AccessHelper.GetInstance().GetEnity<JinShen_CompetSpeech_Main>(condition, "");
            foreach (var m in mains)
            {
                m.Details = AccessHelper.GetInstance().GetEnity<JinShen_CompetSpeech_Detail>("SpeechMainID=" + m.ID, "");
            }
            return mains;
        }

        public bool SaveJinShen_CompetSpeech_Main(JinShen_CompetSpeech_Main entity)
        {
            try
            {
                if (entity.ID > 0)
                {
                    string sql = "delete JinShen_CompetSpeech_Detail where SpeechMainID=" + entity.ID;
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                    int id = AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details" });
                    foreach (var detail in entity.Details)
                    {
                        detail.SpeechMainID = id;
                        detail.ID = 0;
                        AccessHelper.GetInstance().SaveEntity(detail, "", null);
                    }
                }
                else
                {
                    int id = AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details" });
                    foreach (var detail in entity.Details)
                    {
                        detail.SpeechMainID = id;
                        AccessHelper.GetInstance().SaveEntity(detail, "", null);
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public ObservableCollection<JinShen_Democraticevaluation_Main> GetDemocraEvalution(string condition)
        {
            var mains = AccessHelper.GetInstance().GetEnity<JinShen_Democraticevaluation_Main>(condition, "");
            foreach (var m in mains)
            {
                m.Details = AccessHelper.GetInstance().GetEnity<JinShen_Democraticevaluation_Detail>("DemoEvaMainID=" + m.ID, "");
            }
            return mains;
        }

        public bool SaveJinShen_Democraticevaluation_Main(JinShen_Democraticevaluation_Main entity)
        {
            try
            {
                if (entity.ID > 0)
                {
                    string sql = "delete JinShen_Democraticevaluation_Detail where DemoEvaMainID=" + entity.ID;
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                    int id = AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details" });
                    foreach (var detail in entity.Details)
                    {
                        detail.DemoEvaMainID = id;
                        detail.ID = 0;
                        AccessHelper.GetInstance().SaveEntity(detail, "", null);
                    }
                }
                else
                {
                    int id = AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details" });
                    foreach (var detail in entity.Details)
                    {
                        detail.DemoEvaMainID = id;
                        AccessHelper.GetInstance().SaveEntity(detail, "", null);
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public bool SaveDetailCheck(JinShen_Democraticevaluation_Main evaMain, JinShen_CompetSpeech_Main speechMain, JinShen_MainInfo main)
        {
            try
            {
                bo.SaveJinShen_MainInfor(main);
                this.SaveJinShen_Democraticevaluation_Main(evaMain);
                if (speechMain != null)
                {
                    this.SaveJinShen_CompetSpeech_Main(speechMain);
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }


        public string GetOrgForHR(string loginUser)
        {
            return bo.GetOrgForHR(loginUser);
        }


        public bool SaveJinShen_ChooseCandidator_Main(JinShen_ChooseCandidator_Main entity)
        {
            return bo.SaveJinShen_ChooseCandidator_Main(entity);
        }


        public ObservableCollection<JinShen_ChooseCandidator_Main> GetJinShen_ChooseCandidator_Main(string condition)
        {
            ObservableCollection<JinShen_ChooseCandidator_Main> mains = null;
            mains = AccessHelper.GetInstance().GetEnity<JinShen_ChooseCandidator_Main>(condition, "");
            foreach (var main in mains)
            {
                main.Details = AccessHelper.GetInstance().GetEnity<JinShen_ChooseCandidator_Detail>("ChooseMainID=" + main.ID, "");
            }
            return mains;
        }


        public void SaveJinShen_KaoCha_Detail(JinShen_KaoCha_Detail[] details)
        {
            if (details != null && details.Length > 0)
            {
                string sql = "";
                foreach (var item in details)
                {
                    sql += string.Format("delete JinShen_KaoCha_Detail where MainID = {0};" ,item.MainID);
                }
               
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            foreach (var d in details)
            {
                d.ID = 0;
                AccessHelper.GetInstance().SaveEntity(d, "", null);
            }
        }

        public ObservableCollection<JinShen_KaoCha_Detail> GetJinShen_KaoCha_Detail(string condition)
        {
            return AccessHelper.GetInstance().GetEnity<JinShen_KaoCha_Detail>(condition, "");
        }


        public void SaveJinShen_DangGongWei_Detail(JinShen_DangGongWei_Detail[] details)
        {
            if (details != null && details.Length > 0)
            {
                string sql = "";
                foreach (var item in details)
                {
                    sql += string.Format("delete JinShen_DangGongWei_Detail where MainID = {0};", item.MainID);
                }
             
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            foreach (var d in details)
            {
                d.ID = 0;
                AccessHelper.GetInstance().SaveEntity(d, "", null);
            }
        }

        public ObservableCollection<JinShen_DangGongWei_Detail> GetJinShen_DangGongWei_Detail(string condition)
        {
            return AccessHelper.GetInstance().GetEnity<JinShen_DangGongWei_Detail>(condition, "");
        }

        public void SaveJinShen_PublicShow_Detail(JinShen_PublicShow_Detail[] details)
        {
            if (details != null && details.Length > 0)
            {
                 string sql = "";
                 foreach (var item in details)
                 {
                     sql += string.Format("delete JinShen_PublicShow_Detail where MainID ={0}; ", item.MainID);
                 }
                AccessHelper.GetInstance().ExecuteNonQuery(sql);    
            }
            foreach (var d in details)
            {
                d.ID = 0;
                AccessHelper.GetInstance().SaveEntity(d, "", null);
            }
        }

        public ObservableCollection<JinShen_PublicShow_Detail> GetJinShen_PublicShow_Detail(string condition)
        {
            return AccessHelper.GetInstance().GetEnity<JinShen_PublicShow_Detail>(condition, "");
        }





        public string DownLoadFile(string key, string GuidName, string RealName)
        {
            return bo.DownLoadFile(key, GuidName, RealName);
        }


        public bool ComfirmJinShen(int MainInfoID, string Stauts)
        {
            return bo.ComfirmJinShen(MainInfoID, Stauts);
        }

        #region  Emily
        public void SaveJinShenMainInfos(ObservableCollection<JinShen_MainInfo> JinShen_MainInfos, int edit = 0, JinShen_MainInfo_Attatchment att=null)
        {

          int result=  bo.SaveJinShen_MainInfors(JinShen_MainInfos,edit,att);

          
        }

         public int SaveJinShenMainInfos1(ObservableCollection<JinShen_MainInfo> JinShen_MainInfos, int edit = 0, JinShen_MainInfo_Attatchment att = null, ObservableCollection<JinShen_MainInfo_Attatchment> atts = null)
        {
       
           int resultMId= bo.SaveJinShen_MainInfors(JinShen_MainInfos, edit, att);

           //foreach (var item in atts)
           //{
           //    bo.UploadFile1(resultMId, item.Key, item.filename, item.TempUrl, item.realname);

           //}
                return resultMId;
          
        }

         public bool UploadFile1(int Mid, string key, string filename, byte[] content, string realname)
         {
             return bo.UploadFile1(Mid, key, filename, content, realname);
         }
    
        public byte[] DownLoadFile1(string Con)
        {
            //return bo.ReadWord(Convert.ToInt32(Con));

            byte[] OneFile = null;
           
             try
             {
                   string uploadFolder = System.Web.Configuration.WebConfigurationManager.AppSettings["file"].ToString();
             if (File.Exists(uploadFolder + @"\" + Con))
             {
                
                 OneFile = File.ReadAllBytes(uploadFolder + @"\" + Con);
             }
             }
             catch (Exception ex)
             {

                 Log4JClass.SetErro("附件下载",ex);
             }
             return OneFile;
        }
      
        public ObservableCollection<JinShenMHelp> GetJinShen_MainInfos(string condition, string LoginUser)
        {
            return bo.GetJinshenMainInfos(condition, LoginUser);
        }
        #endregion


        public void DeleteJinShenMainInfos(JinShenMHelp entity)
        {
            int Mid = entity.ID;
            AccessHelper.GetInstance().DeleteEntitySql("JinShen_MainInfo", " and Mid="+entity.ID);
            AccessHelper.GetInstance().DeleteEntitySql("JinShen_MainInfo_Attatchment"," and MainID="+entity.ID);
            AccessHelper.GetInstance().DeleteEntity("JinShenM", entity.ID);
        }


        public ObservableCollection<JinShenMHelp> SaveJinShenMainInfosStatus(ObservableCollection<JinShen_MainInfo> JinShen_MainInfos, string status, int Mid, string LoginUser)
        {
            string sql = string.Format(@"UPDATE JinShenM
                                                   SET status='{0}' WHERE id={1} ;
                                                    UPDATE JinShen_MainInfo
                                                   SET status='{0}' WHERE MId={1}", status,Mid);
            AccessHelper.GetInstance().ExecuteNonQuery(sql);

            string keup = "";
            foreach (var item in JinShen_MainInfos)
            {
                keup += string.Format("update JinShen_MainInfo set Department='{0}' where id={1} ;", item.Department, item.ID);
            }
            AccessHelper.GetInstance().ExecuteNonQuery(keup);

            return bo.GetJinshenMainInfos(string.Format(" 1=1 and jsm.ID={0}", Mid), LoginUser);

        }


        public JinShen_XuanJu SaveJinShen_XuanJu(JinShen_XuanJu xuanju, bool isGet)
        {
           
                if (isGet)
                    return AccessHelper.GetInstance().GetEnity<JinShen_XuanJu>("MainID=" + xuanju.MainID, "") == null ? new JinShen_XuanJu() : AccessHelper.GetInstance().GetEnity<JinShen_XuanJu>("MainID=" + xuanju.MainID, "").Count > 0 ? AccessHelper.GetInstance().GetEnity<JinShen_XuanJu>("MainID=" + xuanju.MainID, "").First() : new JinShen_XuanJu();
                else
                {
                    string sql = "delete JinShen_XuanJu where MainID = " + xuanju.MainID;
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);

                    xuanju.ID = 0;
                    AccessHelper.GetInstance().SaveEntity(xuanju, "", null);
                    return null;
                }
        }


        public void SaveJinShen_OKRenM_Detail(JinShen_OKRenM_Detail[] details)
        {
            if (details != null && details.Length > 0)
            {
                string sql = "";
                foreach (var item in details)
                {
                    sql += string.Format("delete JinShen_OKRenM_Detail where MainID ={0}; ", item.MainID);
                }
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            foreach (var d in details)
            {
                d.ID = 0;
                if (string.IsNullOrEmpty(d.SYQYear))
                    d.SYQYear = "0";
                AccessHelper.GetInstance().SaveEntity(d, "", null);
            }
        }


        public ObservableCollection<JinShen_OKRenM_Detail> GetJinJinShen_OKRenM_Detail(string condition)
        {
            return AccessHelper.GetInstance().GetEnity<JinShen_OKRenM_Detail>(condition, "");
        }


        public bool ComfirmJinShens(ObservableCollection<JinShen_MainInfo> JinShen_MainInfos, string Stauts)
        {
            var result = false;
            if (JinShen_MainInfos.Count>0)
            UpdateJinShenMStatus(JinShen_MainInfos.First().MId, Stauts);
            foreach (var item in JinShen_MainInfos)
            {
              
                if(item.OKRenM_Detail.Count!=0)
                result = bo.ComfirmJinShen(item.ID, Stauts);
            }
            return result;
          
        }


        public int UpdateJinShenMStatus(int Mid,string status)
        {
            string sql = "";
            if (status.Equals("已查看"))
                sql = string.Format(@"UPDATE JinShen_MainInfo
                                           SET status='{0}' WHERE Id={1}", status, Mid);
 else if(status.Equals("终止办理"))
                sql = string.Format(@"UPDATE JinShenM
                                                   SET status='{0}' WHERE id={1} ;
                                                    UPDATE JinShen_MainInfo
                                                   SET status='{0}' WHERE MId={1}", status, Mid);
            else
                sql = string.Format(@"UPDATE JinShenM
                                                   SET status='{0}' WHERE id={1} ;
                                                    UPDATE JinShen_MainInfo
                                                   SET status='{0}' WHERE MId={1}", status, Mid);


          return  AccessHelper.GetInstance().ExecuteNonQuery(sql);
        }


        public byte[] ZDYExcel(int OrgId)
        {
            var ds = AccessHelper.GetInstance().ExcuteDataSet(string.Format("exec  [Proc_ZDYExcel] {0}", OrgId));

            HSSFWorkbook workbook = ExportExcel(ds);
            return ExcelToBytes(workbook);
          
        }
        private byte[] ExcelToBytes(HSSFWorkbook workbook)
        {
            byte[] buffer;
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.ToArray();
                ms.Close();
            }

            return buffer;
        }
        private HSSFWorkbook ExportExcel(DataSet ds)
        {

            var hssWorkBook = new HSSFWorkbook();

            HSSFSheet sheet = hssWorkBook.CreateSheet("Sheet1");
            HSSFCellStyle style = hssWorkBook.CreateCellStyle();
            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
            style.BorderLeft = HSSFCellStyle.BORDER_THIN;
            style.BorderTop = HSSFCellStyle.BORDER_THIN;
            style.BorderRight = HSSFCellStyle.BORDER_THIN;
            style.BorderBottom = HSSFCellStyle.BORDER_THIN;


            HSSFCellStyle titleStyle = BuildTitleCellStyle(hssWorkBook);
            HSSFCell celltitle = sheet.CreateRow(0).CreateCell(0);
            celltitle.CellStyle = titleStyle;
            celltitle.SetCellValue("2013年徐汇区科以下公务员队伍结构情况分析表（三）");
            sheet.AddMergedRegion(new CellRangeAddress(0,0,0,21));
            sheet.GetRow(0).Height = 40 * 20;

            HSSFRow row1 = sheet.CreateRow(1);
            row1.CreateCell(2).SetCellValue("性别");
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 2, 3));
            row1.CreateCell(4).SetCellValue("职务属性");
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 4, 5));
            row1.CreateCell(6).SetCellValue("最高学历");
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 6, 10));
            row1.CreateCell(11).SetCellValue("担任现任职务层次属性");
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 11, 16));
            row1.CreateCell(17).SetCellValue("现任职务层次时间");
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 17, 21));

            HSSFRow row2 = sheet.CreateRow(2);
            string[] cols = new string[22] { "", "", "男", "女", "领导职务", "非领导职务", "高中及以下", "大学专科", "大学本科", "硕士研究生", "博士研究生", "未定职", "科员", "副主任科员", "副科长", "主任科员", "正科长", "3年以下", "4年至5年", "6年至10年", "11年至15年", "16年以上" };

            for (int i = 0; i < cols.Length; i++)
            {
                HSSFCell cellCol = row2.CreateCell(i);
                if(i>=2)
                cellCol.SetCellValue(cols[i].ToString());
            }
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 1));
            sheet.GetRow(1).Height = 20 * 20;
            sheet.GetRow(2).Height = 20 * 20;

            //for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
            //{
               
            //    HSSFRow rowValues = sheet.CreateRow(j + 1);

            //    HSSFCell cell = rowValues.CreateCell(0);
            //    cell.SetCellValue(m.XM);
            //    HSSFCell cell1 = rowValues.CreateCell(1);
            //    cell1.SetCellValue(m.XB);
            //    HSSFCell cell2 = rowValues.CreateCell(2);
            //    cell2.SetCellValue(m.ZJHM);
            //    HSSFCell cell3 = rowValues.CreateCell(3);
            //    cell3.SetCellValue(m.SSCJ);
            //    HSSFCell cell4 = rowValues.CreateCell(4);
            //    cell4.SetCellValue(m.JZDXZ);
            //    HSSFCell cell5 = rowValues.CreateCell(5);
            //    cell5.SetCellValue(m.HJDXZ);
            //    HSSFCell cell6 = rowValues.CreateCell(6);
            //    cell6.SetCellValue(m.LXFS);
            //    HSSFCell cell7 = rowValues.CreateCell(7);
            //    cell7.SetCellValue(m.RKLB);
            //    HSSFCell cell8 = rowValues.CreateCell(8);
            //    cell8.SetCellValue(m.MZ);
            //    HSSFCell cell9 = rowValues.CreateCell(9);
            //    cell9.SetCellValue(m.ZZMM);
            //    HSSFCell cell10 = rowValues.CreateCell(10);
            //    cell10.SetCellValue(m.WHCD);
            //    HSSFCell cell11 = rowValues.CreateCell(11);
            //    cell11.SetCellValue(m.HYZK);
            //    HSSFCell cell12 = rowValues.CreateCell(12);
            //    cell12.SetCellValue(m.JG);
            //    HSSFCell cell13 = rowValues.CreateCell(13);
            //    cell13.SetCellValue(m.HJLB);
            //    HSSFCell cell14 = rowValues.CreateCell(14);
            //    cell14.SetCellValue(m.CSD);
            //}
            //#region
            //List<RKXXViewDb> list = model;
            //// 一个工作薄
            //HSSFWorkbook workbook = new HSSFWorkbook();
            //// 一个表格
            //HSSFSheet sheet = workbook.CreateSheet("Sheet1");
            //// 一个标题样式
            //HSSFCellStyle titleStyle = BuildTitleCellStyle(workbook);
            //HSSFCell headCell = sheet.CreateRow(0).CreateCell(1);
            //headCell.CellStyle = titleStyle;
            //headCell.SetCellValue("");
            //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1, 3));
            //sheet.GetRow(0).Height = 40 * 20;
            ////
            //HSSFCellStyle cellStyle = BuildNormalCellStyle(workbook);
            //int row = 1;
            //int col = 1;
            //sheet.SetColumnWidth(1, 500 * 20);
            //sheet.SetColumnWidth(2, 300 * 20);
            //sheet.SetColumnWidth(3, 200 * 20);
            //for (int i = 0; i < 16; i++)  //十六列表
            //{

            //}
            //foreach (var info in list)
            //{
            //    //部门
            //    HSSFCell cell = sheet.CreateRow(row).CreateCell(col);
            //    cell.CellStyle = cellStyle;
            //    cell.SetCellValue(info.OrgName);
            //    //员工
            //    cell = sheet.GetRow(row).CreateCell(col + 1);
            //    cell.CellStyle = cellStyle;
            //    cell.SetCellValue(info.EmpName);
            //    //得分
            //    cell = sheet.GetRow(row).CreateCell(col + 2);
            //    cell.CellStyle = cellStyle;
            //    cell.SetCellValue(string.Format("{0:#######0.##}", info.Score));
            //    sheet.GetRow(row).Height = 20 * 20;
            //    row++;
            //}
            //var orgs = from i in list group i by i.OrgName into g select new { Count = g.Count() };
            //row = 1;
            //foreach (var org in orgs)
            //{
            //    if (org.Count != 1)
            //    {
            //        sheet.AddMergedRegion(new CellRangeAddress(row, row + org.Count - 1, 1, 1));
            //    }
            //    row += org.Count;
            //}
            //#endregion
            return hssWorkBook;
        }
        private HSSFCellStyle BuildTitleCellStyle(HSSFWorkbook workbook)
        {
            // 生成一个样式
            HSSFCellStyle style = workbook.CreateCellStyle();
            // 设置这些样式
            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
            // 生成一个字体
            HSSFFont font = workbook.CreateFont();
            font.FontHeightInPoints = (short)20;
            font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
            // 把字体应用到当前的样式
            style.SetFont(font);
            return style;
        }
        private HSSFCellStyle BuildNormalCellStyle(HSSFWorkbook workbook)
        {
            HSSFCellStyle style = workbook.CreateCellStyle();
            style.BorderLeft = HSSFCellStyle.BORDER_THIN;
            style.BorderTop = HSSFCellStyle.BORDER_THIN;
            style.BorderRight = HSSFCellStyle.BORDER_THIN;
            style.BorderBottom = HSSFCellStyle.BORDER_THIN;

            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
            style.WrapText = true;
            return style;
        }

        #region 准考证

        public void SaveZKZInfo(ZKZInfo entity, bool NewZYSX, string SelEmpIDs,int ExameID)
        {
            //保存注意事项
                AccessHelper.GetInstance().SaveEntity(entity.ZKZZYSXInfoEntity, "", null);
            //保存准考证信息
            AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "ZKZZYSXInfoEntity" });

          

            string sql = string.Format(@"update zkzinfo 
                set zysxId=(select top 1 id from ZKZZYSXInfo where 1=1 order by createdate desc ) 
                ,ZYSXCon=(select top 1 ZYSX from ZKZZYSXInfo where 1=1 order by createdate desc ) 
                where id=(select top 1 id from zkzinfo where 1=1 order by createdate desc);
                update zkzempinfo
                set ZKZID=zkz.ID,KDMC=zkz.KDMC,KDDZ=zkz.KDDZ
                ,KCH=zkz.KCH,KSSJ=zkz.KSSJCOn,ZYSX=zkz.ZYSXCon,Status='已生成',EmpZWH=0
                from zkzempinfo e,zkzinfo zkz
                where e.EmpID in ({0})
                and zkz.ID=(select top 1 ID from ZKZInfo where 1=1 order by CreateDate desc)", SelEmpIDs);
            AccessHelper.GetInstance().ExecuteNonQuery(sql);

            //生成准考证
            var empids = SelEmpIDs.Split(',');
            string sqlzwh = "";
            for (int i = 0; i < empids.Length; i++)
            {
                sqlzwh = string.Format(@"declare @empzwh int
                set @empzwh=0;
                if exists(select top 1 EmpZWH from ZKZEmpInfo where ExameID={0} and KCH='{1}'  order by EmpZWH desc)
                begin
                set @empzwh=(select top 1 EmpZWH from ZKZEmpInfo where ExameID={0} and KCH='{1}' order by EmpZWH desc)
                end
               -- else
               -- begin
               -- update zkzempinfo set KCH='{1}' where ExameID={0} and EmpID={2}
              --  end
                update ZKZEmpInfo set EmpZWH=@empzwh+1 where ExameID={0} and EmpID={2}", ExameID, entity.KCH, empids[i]);

                AccessHelper.GetInstance().ExecuteNonQuery(sqlzwh);
            }
        }


        public ObservableCollection<ZKZZYSXInfo> GetZKZZYSXInfo(string con)
        {
            return AccessHelper.GetInstance().GetEnity<ZKZZYSXInfo>(con, "");
        }


        public ObservableCollection<ZKZEmpInfo> GetZKZEmpInfo(string con)
        {
            string sql = string.Format(@"SELECT zkz.[ID],[EmpID],[EmpName]=eb.Name,[EmpCardNo]=eb.CardNo,PhotoName
	        ,[EmpZCBH],[EmpZKZH],[EmpZWH],[ZKZID],[KDMC],[KDDZ],[KCH],[KSSJ],[ZYSX],[Status]
              ,[ZKZTitle]=(select Name from dbo.PublicOfficialInfo where ID=zkz.ExameID)
              ,[IsSelected]
          FROM ZKZEmpInfo zkz,Emp_BasicInfo eb
          where zkz.EmpID=eb.ID {0}",con);
            return AccessHelper.GetInstance().GetEnityJoin<ZKZEmpInfo>(sql, "", "");
            //return AccessHelper.GetInstance().GetEnity<ZKZEmpInfo>(con, "");
        }
        public ObservableCollection<ZKZEmpInfo> GetZKZEmpInfoByPage(string con,int start,int pagesize)
        {
//            string sql = string.Format(@"select top {0} zkz.[ID],[EmpID],[EmpName]=eb.Name,[EmpCardNo]=eb.CardNo,PhotoName
//            ,[EmpZCBH],[EmpZKZH],[EmpZWH],[ZKZID],[KDMC],[KDDZ],[KCH],[KSSJ],[ZYSX],zkz.[Status]
//            ,[ZKZTitle]=pf.Name,ExameID from ZKZEmpInfo zkz
//            left join Emp_BasicInfo eb on eb.ID=zkz.EmpID
//            left join PublicOfficialInfo pf on pf.ID=zkz.ExameID 
//            where pf.Status=0
//            and zkz.ID>=
//            (
//            SELECT ISNULL(MAX(id),0) FROM 
//            (SELECT TOP ({0}*({1}-1)+1) zkz.id from ZKZEmpInfo zkz
//			left join Emp_BasicInfo eb on eb.ID=zkz.EmpID
//			left join PublicOfficialInfo pf on pf.ID=zkz.ExameID 
//			where pf.Status=0 ORDER BY zkz.id)a
//            )
//            order by zkz.id", pagesize, start);
            string sql = string.Format(@"select top {0} zkz.[ID],zkz.[EmpID],[EmpName]=eb.Name,[EmpCardNo]=eb.CardNo,PhotoName
            ,[EmpZCBH],[EmpZKZH],[EmpZWH],[ZKZID],[KDMC],[KDDZ],[KCH],[KSSJ],[ZYSX],zkz.[Status]
            ,[ZKZTitle]=pf.Name,zkz.ExameID,pr.ID
            ,orgName=oh.Name
            ,ps.Duty
             from ZKZEmpInfo zkz
            left join Emp_BasicInfo eb on eb.ID=zkz.EmpID
            left join PublicOfficialInfo pf on pf.ID=zkz.ExameID
           left join PublicOfficialRecord pr on pr.EmpID=zkz.EmpID and pr.ExameID=pf.ID and GeRenStatus=1
            left join PublicOfficialSelection ps on ps.ID=pr.PublicOfficialSelectionID
             left join OrgForHR oh on oh.ID=ps.OrgID
            where pf.Status=0
            and zkz.ID>=
            (
            SELECT ISNULL(MAX(id),0) FROM 
            (SELECT TOP ({0}*({1}-1)+1) zkz.id from ZKZEmpInfo zkz
			   left join Emp_BasicInfo eb on eb.ID=zkz.EmpID
            left join PublicOfficialInfo pf on pf.ID=zkz.ExameID
           left join PublicOfficialRecord pr on pr.EmpID=zkz.EmpID and pr.ExameID=pf.ID and GeRenStatus=1
            left join PublicOfficialSelection ps on ps.ID=pr.PublicOfficialSelectionID
             left join OrgForHR oh on oh.ID=ps.OrgID
             where pf.Status=0 ORDER BY zkz.id)a
            )
            order by orgName
            ", pagesize, start);
            return AccessHelper.GetInstance().GetEnityJoin<ZKZEmpInfo>(sql, "", "");
   
        }
        public int GetZKZEmpInfoCount(string con)
        {
            string sql = string.Format(@"SELECT Count(*) FROM ZKZEmpInfo zkz,Emp_BasicInfo eb
                             where zkz.EmpID=eb.ID and Status='1' {0}", con);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }

        public void FBEmpZKZ(string con,string status)
        {
            if (!string.IsNullOrEmpty(con))
            {
                string sql = string.Format(@"update zkzempinfo set status='{1}' where id in ({0})", con,status);
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }

        }
        public byte[] DownLoadPhoto(string Con)
        {
            byte[] OneFile = null;
            string uploadFolder = System.Web.Configuration.WebConfigurationManager.AppSettings["photo"].ToString();
            if (File.Exists(uploadFolder + @"\" + Con))
            {

                OneFile = File.ReadAllBytes(uploadFolder + @"\" + Con);
            }
            return OneFile;
        }

        #endregion







        public int GetEmpInfo(string con)
        {
            string sql = string.Format("select COUNT(*) from Emp_BasicInfo where CardNo='{0}'", con);

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }

        public string ExportZKZ(object missingValue, dynamic wordApplication, dynamic doc,string name)
        {
            string saveFolder = System.Web.Hosting.HostingEnvironment.MapPath("~/cash");
            string SavePath = saveFolder + "\\"+name+"准考证" + "-" + System.DateTime.Now.ToString("yyyyMMdd HHmmss");
            wordApplication.ActiveDocument.SaveAs(ref SavePath,
                    ref missingValue, ref missingValue, ref missingValue, ref missingValue,
                    ref missingValue, ref missingValue, ref missingValue, ref missingValue,
                    ref missingValue, ref missingValue, ref missingValue, ref missingValue,
                    ref missingValue, ref missingValue, ref missingValue); //保存文档   
            //如果想要换页，则要用到分页符，代码如下：
            dynamic para;
            para = doc.Content.Paragraphs.Add(ref　missingValue);
            object pBreak = 0;
            para.Range.InsertBreak(ref　pBreak);
            return missingValue.ToString();
        }


        public void SaveBMFBInfo(BMFBInfo entity,string con)
        {
            //保存注意事项
            AccessHelper.GetInstance().SaveEntity(entity, "", null);

        }
        public ObservableCollection<BMFBInfo> GetBMFBInfo(string con)
        {
            string sql = string.Format(@"select * from BMFBInfo order by CreateDate desc ",con);
            return AccessHelper.GetInstance().GetEnityJoin<BMFBInfo>(sql, "", "");

        }


        public ObservableCollection<JinShen_SHJLDetail> GetJinShen_SHJLDetail(string con)
        {
            string sql = string.Format(@"select distinct m.Name,m.Status,m.BLDate,ls.Department,ls.Position,m.JSID from JinShen_SHJLDetail m
left join JinShen_MainInfoLSJL ls on m.JSID=ls.ID where 1=1 {0}", con);
            return AccessHelper.GetInstance().GetEnityJoin<JinShen_SHJLDetail>(sql, "", "");
        }

        public void AddJinShen_SHJLDetail(JinShen_SHJLDetail model,JinShen_MainInfo jsModel)
        {
            string sql="";

            if (model.JSMID <= 0)
                sql = string.Format(@"INSERT INTO JinShen_SHJLDetail([JSMID],[Name])
                select top 1 ID,'{0}' from JinShenM order by ID desc", model.Name);
            else
            {

                if (jsModel.Status == "已查看")
                    sql = string.Format(@"INSERT INTO JinShen_SHJLDetail([JSMID],[Name])
                select {0},'{1}'", jsModel.MId, "人员核定情况");

                sql += " declare @LSJL int,@faty varchar(100)='',@fabz varchar(500)=''";
                string ryty = "";
                if (jsModel.Status == "人员审核不通过" || jsModel.Status == "人员审核通过")
                    ryty = jsModel.Status == "人员审核不通过" ? "不同意" : "同意";
                else if (jsModel.Status == "审核不通过" || jsModel.Status == "审核通过")
                    sql +=string.Format( @"
                    select top 1 @faty=( case when IsPass=1 then'同意'else'不同意' end),@fabz=DSC 
                    from JinShen_MainInfo_Check where MainID={0} order by id desc", jsModel.ID);
                sql += string.Format(@"
            declare @empnames varchar(1000)='', @empname varchar(1000)='',@emps int=0
            select @emps=count(Name) from JinShen_DetailInfo where mainid={19}
            while(@emps>0)
            begin
            select @empname=Name from
            (
            select ROW_NUMBER() over(order by id) rowid,Name from JinShen_DetailInfo  where mainid={19}
            )aa where rowid=@emps

            set @empnames=@empnames+@empname+','
            set @emps=@emps-1
            end
            if(@emps>0)
            set @empnames=LEFT(@empnames,LEN(@empnames)-1)

            INSERT INTO JinShen_MainInfoLSJL
           ([OrgID],[OrgName]  ,[Department],[Position],[PositionAttibute],[PositionLevel],[Education],[Political],[PromotType],[DSC],[Status]
            ,[CreateUser],[FenGuanGongZuo],[MId],FAIsTY,FABeizhu,RYIsTY,RYList,RYBeiZhu,jsid)
            VALUES
           ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}' ,'{9}','{10}','{11}','{12}' ,{13},{14},{15},'{16}',{17},'{18}',{19})
            select @LSJL=@@IDENTITY 
", jsModel.OrgID, jsModel.OrgName, jsModel.Department, jsModel.Position, jsModel.PositionAttibute, jsModel.PositionLevel, jsModel.Education, jsModel.Political, jsModel.PromotType, jsModel.DSC, jsModel.Status
 , jsModel.CreateUser, jsModel.FenGuanGongZuo, jsModel.MId, "@faty", "@fabz", ryty, "@empnames", jsModel.DSC, jsModel.ID);

                sql += string.Format(@"if exists(select * from JinShen_SHJLDetail where jsmid={0} and name='{1}' and ( CreateUser='' or CreateUser is null))
                begin
                delete JinShen_SHJLDetail where jsmid={0} and name='{1}' and ( CreateUser='' or CreateUser is null)
                end
                INSERT INTO JinShen_SHJLDetail
                           ([JSMID],[Name],[Status],[BLDate],[CreateDate],[CreateUser],JSID)
                                     VALUES({0},'{1}','{2}','{3}','{4}','{5}',@LSJL)", model.JSMID, model.Name, model.Status, model.BLDate, model.CreateDate, model.CreateUser, jsModel.ID);
             
            }
            AccessHelper.GetInstance().ExecuteNonQuery(sql);
        }

        public ObservableCollection<JinShenMHelp> GetJinShen_MainInfosByCon(string condition, string LoginUser,int start,int pagesize)
        {
            return bo.GetJinshenMainInfosByCon(condition, LoginUser, start, pagesize);
        }


        public int GetJinShen_MainInfosByCount(string condition, string LoginUser)
        {
            string sql = string.Format(@"select r.RoleName,u.OrgCode from UserInfo u
left join Roles r on r.id = u.roleID
where u.loginname='{0}'", LoginUser);
            List<string> SuperAdmin = new List<string>()
            {
                "管理员",
                "系统操作"
            };

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            string role = ds.Tables[0].Rows[0][0] as string;
            string code = ds.Tables[0].Rows[0][1] as string;
            if (!SuperAdmin.Contains(role))
            {
                if (string.IsNullOrEmpty(condition))
                {
                    //  condition = string.Format(" and CreateUser = '{0}'", LoginUser);
                    condition = string.Format(" and jsm.Orgid in ({0})", code);
                }
                else
                {
                    //  condition += string.Format("and CreateUser = '{0}'", LoginUser);
                    condition += string.Format(" and jsm.Orgid in ({0}) ", code);
                }
            }

            string sql1 = @"select COUNT(jsm.ID)
from dbo.JinShenM jsm left join JinShen_MainInfo_Attatchment jma on jsm.id=jma.MainID and jma.IsBTg=0 where 1=1";
            if (!string.IsNullOrEmpty(condition))
            {
                sql1 += condition;
            }

             ds = AccessHelper.GetInstance().ExcuteDataSet(sql1);
            return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }




        public JinShen_MainInfoLSJL GetJinShen_MainInfoLSJL(string con)
        {
            JinShen_MainInfoLSJL item = new JinShen_MainInfoLSJL();
            item = AccessHelper.GetInstance().GetEnity<JinShen_MainInfoLSJL>("ID=" + con, "").First();

            item.JinShen_MainInfoInfo = AccessHelper.GetInstance().GetEnity<JinShen_MainInfo>("ID=" + item.jsid, "");
            item.jsfile = AccessHelper.GetInstance().GetEnity<JinShen_MainInfo_Attatchment>("IsBTg =1 and MainID=" + item.MId, "");
            #region
            //item.Details = AccessHelper.GetInstance().GetEnity<JinShen_DetailInfo>("MainID=" + item.jsid, "");

                //item.ChooseCandidator_Main = AccessHelper.GetInstance().GetEnity<JinShen_ChooseCandidator_Main>("MainID=" + item.jsid, "");
                //foreach (var main1 in item.ChooseCandidator_Main)
                //{
                //    main1.Details = AccessHelper.GetInstance().GetEnity<JinShen_ChooseCandidator_Detail>("ChooseMainID=" + main1.ID, "");
                //}
                //item.KaoCha_Detail = AccessHelper.GetInstance().GetEnity<JinShen_KaoCha_Detail>("MainID=" + item.jsid, "");

                //item.DangGongWei_Detail = AccessHelper.GetInstance().GetEnity<JinShen_DangGongWei_Detail>("MainID=" + item.jsid, "");

                //item.PublicShow_Detail = AccessHelper.GetInstance().GetEnity<JinShen_PublicShow_Detail>("MainID=" + item.jsid, "");

            //item.OKRenM_Detail = AccessHelper.GetInstance().GetEnity<JinShen_OKRenM_Detail>("MainID=" + item.jsid, "");
            #endregion

            return item;
        }

        #region  系统消息
        public ObservableCollection<SystemInfo> GetSystemInfo(string conn,int start, int pagesize)
        {
            return SysInfoBO.GetSystemInfoByConn(conn, start, pagesize);
        }

        public int GetSystemInfoByCount(string conn)
        {
            return SysInfoBO.GetSystemInfoByCount(conn);
        }
        public void UpdateSystemInfoByCount(string conn)
        {
             SysInfoBO.UpdateSystemInfoByCount(conn);
        }
        public void AddSystemInfo(SystemInfo sysinfo)
        {
            SysInfoBO.AddSystemInfo(sysinfo);
        }
        #endregion










        #region IJinShengService 成员


        public bool DeleJS(int id)
        {
            //throw new NotImplementedException();
            string sql = string.Format("delete JinShen_MainInfo where id={0}",id);
            if (AccessHelper.GetInstance().ExecuteNonQuery(sql) > 0)
                return true;
            else return false;
          
        }

        #endregion

        #region  处级晋升


        public bool SaveJY(Emp_CJJS entity, bool isAdd)
        {
            return new JSCJBO().SaveJY(entity, isAdd);
        }
        public bool JYDel(Emp_CJJS entity)
        {
            return new JSCJBO().JYDel(entity);
        }

        public ObservableCollection<Emp_CJJS> GetJY(string con, int index, int pagesize)
        {
            return new JSCJBO().GetJY(con, index, pagesize);
        }

        public int GetJYCount(string con)
        {
            return new JSCJBO().GetJYCount(con);
        }
        #endregion

    }
}
